{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:32.473126Z",
     "end_time": "2024-06-18T17:15:32.994636Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "   cust_id           type  Monetary\n0    10001         Normal      3608\n1    10001  Special_offer       420\n2    10002         Normal      1894\n3    10002  Special_offer      3503\n4    10003  Special_offer      4567",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>cust_id</th>\n      <th>type</th>\n      <th>Monetary</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>10001</td>\n      <td>Normal</td>\n      <td>3608</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>10001</td>\n      <td>Special_offer</td>\n      <td>420</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>10002</td>\n      <td>Normal</td>\n      <td>1894</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>10002</td>\n      <td>Special_offer</td>\n      <td>3503</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>10003</td>\n      <td>Special_offer</td>\n      <td>4567</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "table = pd.DataFrame({'cust_id': [10001, 10001, 10002, 10002, 10003],\n",
    "                      'type': ['Normal', 'Special_offer', \\\n",
    "                               'Normal', 'Special_offer', 'Special_offer'],\n",
    "                      'Monetary': [3608, 420, 1894, 3503, 4567]})\n",
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [
    {
     "data": {
      "text/plain": "type     Normal  Special_offer\ncust_id                       \n10001    3608.0          420.0\n10002    1894.0         3503.0\n10003       NaN         4567.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type</th>\n      <th>Normal</th>\n      <th>Special_offer</th>\n    </tr>\n    <tr>\n      <th>cust_id</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10001</th>\n      <td>3608.0</td>\n      <td>420.0</td>\n    </tr>\n    <tr>\n      <th>10002</th>\n      <td>1894.0</td>\n      <td>3503.0</td>\n    </tr>\n    <tr>\n      <th>10003</th>\n      <td>NaN</td>\n      <td>4567.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.pivot_table(table, index='cust_id', columns='type', values='Monetary')\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:32.996634Z",
     "end_time": "2024-06-18T17:15:33.010913Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "data": {
      "text/plain": "type     Normal  Special_offer\ncust_id                       \n10001      3608            420\n10002      1894           3503\n10003         0           4567",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type</th>\n      <th>Normal</th>\n      <th>Special_offer</th>\n    </tr>\n    <tr>\n      <th>cust_id</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10001</th>\n      <td>3608</td>\n      <td>420</td>\n    </tr>\n    <tr>\n      <th>10002</th>\n      <td>1894</td>\n      <td>3503</td>\n    </tr>\n    <tr>\n      <th>10003</th>\n      <td>0</td>\n      <td>4567</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(table, index='cust_id', columns='type', values='Monetary',\n",
    "               fill_value=0, aggfunc='sum')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.009926Z",
     "end_time": "2024-06-18T17:15:33.062725Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "data": {
      "text/plain": "type  cust_id  Normal  Special_offer\n0       10001    3608            420\n1       10002    1894           3503\n2       10003       0           4567",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type</th>\n      <th>cust_id</th>\n      <th>Normal</th>\n      <th>Special_offer</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>10001</td>\n      <td>3608</td>\n      <td>420</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>10002</td>\n      <td>1894</td>\n      <td>3503</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>10003</td>\n      <td>0</td>\n      <td>4567</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table1 = pd.pivot_table(table, index='cust_id',\n",
    "                        columns='type',\n",
    "                        values='Monetary',\n",
    "                        fill_value=0,\n",
    "                        aggfunc='sum').reset_index()\n",
    "table1"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.024171Z",
     "end_time": "2024-06-18T17:15:33.108596Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "   cust_id           TYPE  Monetary\n0    10001         Normal      3608\n1    10002         Normal      1894\n2    10003         Normal         0\n3    10001  Special_offer       420\n4    10002  Special_offer      3503\n5    10003  Special_offer      4567",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>cust_id</th>\n      <th>TYPE</th>\n      <th>Monetary</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>10001</td>\n      <td>Normal</td>\n      <td>3608</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>10002</td>\n      <td>Normal</td>\n      <td>1894</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>10003</td>\n      <td>Normal</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>10001</td>\n      <td>Special_offer</td>\n      <td>420</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>10002</td>\n      <td>Special_offer</td>\n      <td>3503</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>10003</td>\n      <td>Special_offer</td>\n      <td>4567</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.melt(table1,\n",
    "        id_vars='cust_id',\n",
    "        value_vars=['Normal', 'Special_offer'],\n",
    "        value_name='Monetary',\n",
    "        var_name='TYPE')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.037126Z",
     "end_time": "2024-06-18T17:15:33.130977Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "   transID  cumid              time  amount type_label            type\n0     9407  10001  14JUN09:17:58:34   199.0         正常          Normal\n1     9625  10001  16JUN09:15:09:13   369.0         正常          Normal\n2    11837  10001  01JUL09:14:50:36   369.0         正常          Normal\n3    26629  10001  14DEC09:18:05:32   359.0         正常          Normal\n4    30850  10001  12APR10:13:02:20   399.0         正常          Normal\n5    32007  10001  04MAY10:16:45:58   269.0         正常          Normal\n6    36637  10001  04JUN10:20:03:06     0.0         赠送       Presented\n7    43108  10001  06JUL10:16:56:40   381.0         正常          Normal\n8    43877  10001  10JUL10:20:41:54  -399.0         退货  returned_goods\n9    46081  10001  23JUL10:16:35:45     0.0         赠送       Presented",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>transID</th>\n      <th>cumid</th>\n      <th>time</th>\n      <th>amount</th>\n      <th>type_label</th>\n      <th>type</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>9407</td>\n      <td>10001</td>\n      <td>14JUN09:17:58:34</td>\n      <td>199.0</td>\n      <td>正常</td>\n      <td>Normal</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>9625</td>\n      <td>10001</td>\n      <td>16JUN09:15:09:13</td>\n      <td>369.0</td>\n      <td>正常</td>\n      <td>Normal</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>11837</td>\n      <td>10001</td>\n      <td>01JUL09:14:50:36</td>\n      <td>369.0</td>\n      <td>正常</td>\n      <td>Normal</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>26629</td>\n      <td>10001</td>\n      <td>14DEC09:18:05:32</td>\n      <td>359.0</td>\n      <td>正常</td>\n      <td>Normal</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>30850</td>\n      <td>10001</td>\n      <td>12APR10:13:02:20</td>\n      <td>399.0</td>\n      <td>正常</td>\n      <td>Normal</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>32007</td>\n      <td>10001</td>\n      <td>04MAY10:16:45:58</td>\n      <td>269.0</td>\n      <td>正常</td>\n      <td>Normal</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>36637</td>\n      <td>10001</td>\n      <td>04JUN10:20:03:06</td>\n      <td>0.0</td>\n      <td>赠送</td>\n      <td>Presented</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>43108</td>\n      <td>10001</td>\n      <td>06JUL10:16:56:40</td>\n      <td>381.0</td>\n      <td>正常</td>\n      <td>Normal</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>43877</td>\n      <td>10001</td>\n      <td>10JUL10:20:41:54</td>\n      <td>-399.0</td>\n      <td>退货</td>\n      <td>returned_goods</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>46081</td>\n      <td>10001</td>\n      <td>23JUL10:16:35:45</td>\n      <td>0.0</td>\n      <td>赠送</td>\n      <td>Presented</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trad_flow = pd.read_csv(r'../data/RFM_TRAD_FLOW.csv', encoding='gbk')\n",
    "trad_flow.head(10)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.050706Z",
     "end_time": "2024-06-18T17:15:33.197502Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "                      amount\ncumid type                  \n10001 Normal          3608.0\n      Presented          0.0\n      Special_offer    420.0\n      returned_goods  -694.0\n10002 Normal          1894.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>amount</th>\n    </tr>\n    <tr>\n      <th>cumid</th>\n      <th>type</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">10001</th>\n      <th>Normal</th>\n      <td>3608.0</td>\n    </tr>\n    <tr>\n      <th>Presented</th>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>Special_offer</th>\n      <td>420.0</td>\n    </tr>\n    <tr>\n      <th>returned_goods</th>\n      <td>-694.0</td>\n    </tr>\n    <tr>\n      <th>10002</th>\n      <th>Normal</th>\n      <td>1894.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "M = trad_flow.groupby(['cumid', 'type'])[['amount']].sum()\n",
    "M.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.095770Z",
     "end_time": "2024-06-18T17:15:33.221499Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "data": {
      "text/plain": "type   Normal  Presented  Special_offer  returned_goods\ncumid                                                  \n10001  3608.0        0.0          420.0          -694.0\n10002  1894.0        0.0            NaN          -242.0\n10003  3503.0        0.0          156.0          -224.0\n10004  2979.0        0.0          373.0           -40.0\n10005  2368.0        0.0            NaN          -249.0\n...       ...        ...            ...             ...\n40296  2679.5        0.0          224.0          -249.0\n40297  3928.0        0.0          732.0          -356.0\n40298  2284.0        0.0          588.0             NaN\n40299  3763.0        0.0          332.0             NaN\n40300  1363.0        0.0          125.0          -757.0\n\n[1200 rows x 4 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type</th>\n      <th>Normal</th>\n      <th>Presented</th>\n      <th>Special_offer</th>\n      <th>returned_goods</th>\n    </tr>\n    <tr>\n      <th>cumid</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10001</th>\n      <td>3608.0</td>\n      <td>0.0</td>\n      <td>420.0</td>\n      <td>-694.0</td>\n    </tr>\n    <tr>\n      <th>10002</th>\n      <td>1894.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>-242.0</td>\n    </tr>\n    <tr>\n      <th>10003</th>\n      <td>3503.0</td>\n      <td>0.0</td>\n      <td>156.0</td>\n      <td>-224.0</td>\n    </tr>\n    <tr>\n      <th>10004</th>\n      <td>2979.0</td>\n      <td>0.0</td>\n      <td>373.0</td>\n      <td>-40.0</td>\n    </tr>\n    <tr>\n      <th>10005</th>\n      <td>2368.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>-249.0</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>40296</th>\n      <td>2679.5</td>\n      <td>0.0</td>\n      <td>224.0</td>\n      <td>-249.0</td>\n    </tr>\n    <tr>\n      <th>40297</th>\n      <td>3928.0</td>\n      <td>0.0</td>\n      <td>732.0</td>\n      <td>-356.0</td>\n    </tr>\n    <tr>\n      <th>40298</th>\n      <td>2284.0</td>\n      <td>0.0</td>\n      <td>588.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>40299</th>\n      <td>3763.0</td>\n      <td>0.0</td>\n      <td>332.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>40300</th>\n      <td>1363.0</td>\n      <td>0.0</td>\n      <td>125.0</td>\n      <td>-757.0</td>\n    </tr>\n  </tbody>\n</table>\n<p>1200 rows × 4 columns</p>\n</div>"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "M_trans = pd.pivot_table(M, index='cumid', columns='type', values='amount')\n",
    "M_trans"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.110596Z",
     "end_time": "2024-06-18T17:15:33.222511Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "data": {
      "text/plain": "                      transID\ncumid type                   \n10001 Normal               15\n      Presented             8\n      Special_offer         2\n      returned_goods        2\n10002 Normal               12",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>transID</th>\n    </tr>\n    <tr>\n      <th>cumid</th>\n      <th>type</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">10001</th>\n      <th>Normal</th>\n      <td>15</td>\n    </tr>\n    <tr>\n      <th>Presented</th>\n      <td>8</td>\n    </tr>\n    <tr>\n      <th>Special_offer</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>returned_goods</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>10002</th>\n      <th>Normal</th>\n      <td>12</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "F = trad_flow.groupby(['cumid', 'type'])[['transID']].count()\n",
    "F.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.128976Z",
     "end_time": "2024-06-18T17:15:33.222511Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [
    {
     "data": {
      "text/plain": "type   Normal  Presented  Special_offer  returned_goods\ncumid                                                  \n10001    15.0        8.0            2.0             2.0\n10002    12.0        5.0            NaN             1.0\n10003    15.0        8.0            1.0             1.0\n10004    15.0       12.0            2.0             1.0\n10005     8.0        5.0            NaN             1.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type</th>\n      <th>Normal</th>\n      <th>Presented</th>\n      <th>Special_offer</th>\n      <th>returned_goods</th>\n    </tr>\n    <tr>\n      <th>cumid</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10001</th>\n      <td>15.0</td>\n      <td>8.0</td>\n      <td>2.0</td>\n      <td>2.0</td>\n    </tr>\n    <tr>\n      <th>10002</th>\n      <td>12.0</td>\n      <td>5.0</td>\n      <td>NaN</td>\n      <td>1.0</td>\n    </tr>\n    <tr>\n      <th>10003</th>\n      <td>15.0</td>\n      <td>8.0</td>\n      <td>1.0</td>\n      <td>1.0</td>\n    </tr>\n    <tr>\n      <th>10004</th>\n      <td>15.0</td>\n      <td>12.0</td>\n      <td>2.0</td>\n      <td>1.0</td>\n    </tr>\n    <tr>\n      <th>10005</th>\n      <td>8.0</td>\n      <td>5.0</td>\n      <td>NaN</td>\n      <td>1.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "F_trans = pd.pivot_table(F, index='cumid', columns='type', values='transID')\n",
    "F_trans.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.143514Z",
     "end_time": "2024-06-18T17:15:33.248495Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [
    {
     "data": {
      "text/plain": "                                  time\ncumid type                            \n10001 Normal          21JUL09:09:31:26\n      Presented       31MAR10:20:29:48\n      Special_offer   12OCT09:10:59:13\n      returned_goods  10JUL10:20:41:54\n10002 Normal          29JUL09:19:21:41",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>time</th>\n    </tr>\n    <tr>\n      <th>cumid</th>\n      <th>type</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">10001</th>\n      <th>Normal</th>\n      <td>21JUL09:09:31:26</td>\n    </tr>\n    <tr>\n      <th>Presented</th>\n      <td>31MAR10:20:29:48</td>\n    </tr>\n    <tr>\n      <th>Special_offer</th>\n      <td>12OCT09:10:59:13</td>\n    </tr>\n    <tr>\n      <th>returned_goods</th>\n      <td>10JUL10:20:41:54</td>\n    </tr>\n    <tr>\n      <th>10002</th>\n      <th>Normal</th>\n      <td>29JUL09:19:21:41</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "R = trad_flow.groupby(['cumid', 'type'])[['time']].max()\n",
    "R.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.157497Z",
     "end_time": "2024-06-18T17:15:33.593735Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "data": {
      "text/plain": "type   Normal  Presented  Special_offer  returned_goods  spe_ratio\ncumid                                                             \n10001  3608.0        0.0          420.0          -694.0   0.104270\n10002  1894.0        0.0            0.0          -242.0   0.000000\n10003  3503.0        0.0          156.0          -224.0   0.042635\n10004  2979.0        0.0          373.0           -40.0   0.111277\n10005  2368.0        0.0            0.0          -249.0   0.000000",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type</th>\n      <th>Normal</th>\n      <th>Presented</th>\n      <th>Special_offer</th>\n      <th>returned_goods</th>\n      <th>spe_ratio</th>\n    </tr>\n    <tr>\n      <th>cumid</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10001</th>\n      <td>3608.0</td>\n      <td>0.0</td>\n      <td>420.0</td>\n      <td>-694.0</td>\n      <td>0.104270</td>\n    </tr>\n    <tr>\n      <th>10002</th>\n      <td>1894.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>-242.0</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>10003</th>\n      <td>3503.0</td>\n      <td>0.0</td>\n      <td>156.0</td>\n      <td>-224.0</td>\n      <td>0.042635</td>\n    </tr>\n    <tr>\n      <th>10004</th>\n      <td>2979.0</td>\n      <td>0.0</td>\n      <td>373.0</td>\n      <td>-40.0</td>\n      <td>0.111277</td>\n    </tr>\n    <tr>\n      <th>10005</th>\n      <td>2368.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>-249.0</td>\n      <td>0.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "M_trans['Special_offer'] = M_trans['Special_offer'].fillna(0)\n",
    "M_trans['spe_ratio'] = M_trans['Special_offer'] / (M_trans['Special_offer'] + M_trans['Normal'])\n",
    "M_trans.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.354123Z",
     "end_time": "2024-06-18T17:15:33.619742Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [
    {
     "data": {
      "text/plain": "type   Normal  Presented  Special_offer  returned_goods  spe_ratio\ncumid                                                             \n10151   765.0        0.0          870.0             NaN   0.532110\n40033  1206.0        0.0          761.0          -848.0   0.386884\n40236  1155.0        0.0          691.0          -793.0   0.374323\n30225  1475.0        0.0          738.0          -301.0   0.333484\n20068  1631.0        0.0          731.0          -239.0   0.309483\n...       ...        ...            ...             ...        ...\n30205  3770.0        0.0            0.0             NaN   0.000000\n30203  1406.0        0.0            0.0             NaN   0.000000\n20043  3124.0        0.0            0.0          -663.0   0.000000\n30194  4366.0        0.0            0.0           -89.0   0.000000\n20019  3735.0        0.0            0.0          -319.0   0.000000\n\n[1200 rows x 5 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type</th>\n      <th>Normal</th>\n      <th>Presented</th>\n      <th>Special_offer</th>\n      <th>returned_goods</th>\n      <th>spe_ratio</th>\n    </tr>\n    <tr>\n      <th>cumid</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10151</th>\n      <td>765.0</td>\n      <td>0.0</td>\n      <td>870.0</td>\n      <td>NaN</td>\n      <td>0.532110</td>\n    </tr>\n    <tr>\n      <th>40033</th>\n      <td>1206.0</td>\n      <td>0.0</td>\n      <td>761.0</td>\n      <td>-848.0</td>\n      <td>0.386884</td>\n    </tr>\n    <tr>\n      <th>40236</th>\n      <td>1155.0</td>\n      <td>0.0</td>\n      <td>691.0</td>\n      <td>-793.0</td>\n      <td>0.374323</td>\n    </tr>\n    <tr>\n      <th>30225</th>\n      <td>1475.0</td>\n      <td>0.0</td>\n      <td>738.0</td>\n      <td>-301.0</td>\n      <td>0.333484</td>\n    </tr>\n    <tr>\n      <th>20068</th>\n      <td>1631.0</td>\n      <td>0.0</td>\n      <td>731.0</td>\n      <td>-239.0</td>\n      <td>0.309483</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>30205</th>\n      <td>3770.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>30203</th>\n      <td>1406.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>20043</th>\n      <td>3124.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>-663.0</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>30194</th>\n      <td>4366.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>-89.0</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>20019</th>\n      <td>3735.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>-319.0</td>\n      <td>0.000000</td>\n    </tr>\n  </tbody>\n</table>\n<p>1200 rows × 5 columns</p>\n</div>"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "M_rank = M_trans.sort_values('spe_ratio', ascending=False, na_position='last')\n",
    "M_rank"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.368077Z",
     "end_time": "2024-06-18T17:15:33.619742Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [
    {
     "data": {
      "text/plain": "type   Normal  Presented  Special_offer  returned_goods  spe_ratio  \\\ncumid                                                                \n10151   765.0        0.0          870.0             NaN   0.532110   \n40033  1206.0        0.0          761.0          -848.0   0.386884   \n40236  1155.0        0.0          691.0          -793.0   0.374323   \n30225  1475.0        0.0          738.0          -301.0   0.333484   \n20068  1631.0        0.0          731.0          -239.0   0.309483   \n\ntype   spe_ratio_group  \ncumid                   \n10151  (0.0972, 0.532]  \n40033  (0.0972, 0.532]  \n40236  (0.0972, 0.532]  \n30225  (0.0972, 0.532]  \n20068  (0.0972, 0.532]  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>type</th>\n      <th>Normal</th>\n      <th>Presented</th>\n      <th>Special_offer</th>\n      <th>returned_goods</th>\n      <th>spe_ratio</th>\n      <th>spe_ratio_group</th>\n    </tr>\n    <tr>\n      <th>cumid</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10151</th>\n      <td>765.0</td>\n      <td>0.0</td>\n      <td>870.0</td>\n      <td>NaN</td>\n      <td>0.532110</td>\n      <td>(0.0972, 0.532]</td>\n    </tr>\n    <tr>\n      <th>40033</th>\n      <td>1206.0</td>\n      <td>0.0</td>\n      <td>761.0</td>\n      <td>-848.0</td>\n      <td>0.386884</td>\n      <td>(0.0972, 0.532]</td>\n    </tr>\n    <tr>\n      <th>40236</th>\n      <td>1155.0</td>\n      <td>0.0</td>\n      <td>691.0</td>\n      <td>-793.0</td>\n      <td>0.374323</td>\n      <td>(0.0972, 0.532]</td>\n    </tr>\n    <tr>\n      <th>30225</th>\n      <td>1475.0</td>\n      <td>0.0</td>\n      <td>738.0</td>\n      <td>-301.0</td>\n      <td>0.333484</td>\n      <td>(0.0972, 0.532]</td>\n    </tr>\n    <tr>\n      <th>20068</th>\n      <td>1631.0</td>\n      <td>0.0</td>\n      <td>731.0</td>\n      <td>-239.0</td>\n      <td>0.309483</td>\n      <td>(0.0972, 0.532]</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "M_rank['spe_ratio_group'] = pd.qcut(M_rank['spe_ratio'], 4)  # 这里以age_oldest_tr字段等宽分为4段\n",
    "M_rank.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.382058Z",
     "end_time": "2024-06-18T17:15:33.619742Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-06-18T17:15:33.398463Z",
     "end_time": "2024-06-18T17:15:33.619742Z"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
